/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * MainFrame.java
 *
 * Created on May 31, 2011, 3:49:51 PM
 */
package newgen.importroutine;

import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.RandomAccessFile;
import java.io.Reader;
import java.io.Writer;
import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Locale;
import java.util.StringTokenizer;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.SwingWorker;
import javax.swing.filechooser.FileFilter;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.XMLStreamConstants;
import javax.xml.stream.XMLStreamReader;
import newgenlib.marccomponent.conversion.Converter;
import newgenlib.marccomponent.marcmodel.CatalogMaterialDescription;
import newgenlib.marccomponent.marcmodel.ControlField;
import newgenlib.marccomponent.marcmodel.Field;
import newgenlib.marccomponent.marcmodel.FixedFieldProcessor;
import newgenlib.marccomponent.marcmodel.Leader;
import newgenlib.marccomponent.marcmodel.SubField;
import newgenlib.marccomponent.persistence.PersistMARCRecord;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.solr.client.solrj.SolrQuery;
import org.apache.solr.client.solrj.SolrServer;
import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.impl.CommonsHttpSolrServer;
import org.apache.solr.client.solrj.response.QueryResponse;
import org.apache.solr.common.SolrDocumentList;
import org.marc4j.marc.Subfield;
import org.verus.ngl.indexing.CorporateNameSolrIndexCreator;
import org.verus.ngl.indexing.FormGenreSolrIndexCreator;
import org.verus.ngl.indexing.MeetingNameSolrIndexCreator;
import org.verus.ngl.indexing.NewBibliographicSolrIndexCreator;
import org.verus.ngl.indexing.PersonalNameSolrIndexCreator;
import org.verus.ngl.indexing.SubGeoNameSolrIndexCreator;
import org.verus.ngl.indexing.SubTopicSolrIndexCreator;
import org.verus.ngl.indexing.UniformTitleSolrIndexCreator;

/**
 *
 * @author siddartha
 */
public class MainFrame extends javax.swing.JFrame {

    Connection con = null;
    char recorDelim = '';
    NewBibliographicSolrIndexCreator nbw = new NewBibliographicSolrIndexCreator();

    /** Creates new form MainFrame */
    public MainFrame() {
        initComponents();
        
        //jPanel8.setVisible(false);
        tfDBIPAddress.setText("localhost");
        tfDBName.setText("IITHF1");
        tfDBPort.setText("5432");
        tfDBUsername.setText("newgenlib");
        tfDBPassword.setText("newgenlib");
        dialogSaveISBNs.setSize(250, 100);
        try {
            Class.forName("org.postgresql.Driver");
            this.con = DriverManager.getConnection("jdbc:postgresql://" + tfDBIPAddress.getText() + ":" + tfDBPort.getText() + "/" + tfDBName.getText(), tfDBUsername.getText(), tfDBPassword.getText());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {
        java.awt.GridBagConstraints gridBagConstraints;

        dialogSaveISBNs = new javax.swing.JDialog();
        jPanel4 = new javax.swing.JPanel();
        jLabel3 = new javax.swing.JLabel();
        tfTxtFilePath = new javax.swing.JTextField();
        bnISBNFileLocation = new javax.swing.JButton();
        jPanel5 = new javax.swing.JPanel();
        bnISBNSaveOk = new javax.swing.JButton();
        bnCancel = new javax.swing.JButton();
        jPanel3 = new javax.swing.JPanel();
        jLabel2 = new javax.swing.JLabel();
        tfExcelSheet = new javax.swing.JTextField();
        jButton4 = new javax.swing.JButton();
        jButton5 = new javax.swing.JButton();
        jPanel1 = new javax.swing.JPanel();
        jLabel1 = new javax.swing.JLabel();
        jTextField1 = new javax.swing.JTextField();
        jButton1 = new javax.swing.JButton();
        jButton2 = new javax.swing.JButton();
        jPanel6 = new javax.swing.JPanel();
        jLabel4 = new javax.swing.JLabel();
        tfDBIPAddress = new javax.swing.JTextField();
        jLabel5 = new javax.swing.JLabel();
        tfDBPort = new javax.swing.JTextField();
        jLabel6 = new javax.swing.JLabel();
        tfDBUsername = new javax.swing.JTextField();
        jLabel7 = new javax.swing.JLabel();
        tfDBPassword = new javax.swing.JTextField();
        jLabel8 = new javax.swing.JLabel();
        jLabel9 = new javax.swing.JLabel();
        tfDBName = new javax.swing.JTextField();
        jPanel8 = new javax.swing.JPanel();
        jLabel10 = new javax.swing.JLabel();
        bnInsertAccessionnos = new javax.swing.JButton();
        jPanel7 = new javax.swing.JPanel();
        pbImportFile = new javax.swing.JProgressBar();
        jPanel2 = new javax.swing.JPanel();
        jButton3 = new javax.swing.JButton();

        dialogSaveISBNs.setTitle("Save isbn data");
        dialogSaveISBNs.getContentPane().setLayout(new javax.swing.BoxLayout(dialogSaveISBNs.getContentPane(), javax.swing.BoxLayout.Y_AXIS));

        jPanel4.setBorder(javax.swing.BorderFactory.createEtchedBorder());

        jLabel3.setText("File name ");
        jPanel4.add(jLabel3);

        tfTxtFilePath.setColumns(10);
        jPanel4.add(tfTxtFilePath);

        bnISBNFileLocation.setText("...");
        bnISBNFileLocation.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bnISBNFileLocationActionPerformed(evt);
            }
        });
        jPanel4.add(bnISBNFileLocation);

        dialogSaveISBNs.getContentPane().add(jPanel4);

        bnISBNSaveOk.setText("Ok");
        bnISBNSaveOk.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bnISBNSaveOkActionPerformed(evt);
            }
        });
        jPanel5.add(bnISBNSaveOk);

        bnCancel.setText("Cancel");
        bnCancel.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bnCancelActionPerformed(evt);
            }
        });
        jPanel5.add(bnCancel);

        dialogSaveISBNs.getContentPane().add(jPanel5);

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        getContentPane().setLayout(new javax.swing.BoxLayout(getContentPane(), javax.swing.BoxLayout.Y_AXIS));

        jPanel3.setLayout(new java.awt.GridBagLayout());

        jLabel2.setText("Select the excel sheet");
        jPanel3.add(jLabel2, new java.awt.GridBagConstraints());

        tfExcelSheet.setColumns(15);
        jPanel3.add(tfExcelSheet, new java.awt.GridBagConstraints());

        jButton4.setText("...");
        jButton4.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton4ActionPerformed(evt);
            }
        });
        jPanel3.add(jButton4, new java.awt.GridBagConstraints());

        jButton5.setText("Generate ISBNs text file for MARC Edit");
        jButton5.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton5ActionPerformed(evt);
            }
        });
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridy = 1;
        gridBagConstraints.gridwidth = 3;
        gridBagConstraints.fill = java.awt.GridBagConstraints.HORIZONTAL;
        jPanel3.add(jButton5, gridBagConstraints);

        getContentPane().add(jPanel3);

        jPanel1.setLayout(new java.awt.GridBagLayout());

        jLabel1.setText("Select the MARC XML file");
        jPanel1.add(jLabel1, new java.awt.GridBagConstraints());

        jTextField1.setColumns(15);
        jTextField1.setEditable(false);
        jPanel1.add(jTextField1, new java.awt.GridBagConstraints());

        jButton1.setText("...");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });
        jPanel1.add(jButton1, new java.awt.GridBagConstraints());

        jButton2.setText("Show me a report of the above selected file");
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridy = 1;
        gridBagConstraints.gridwidth = 3;
        gridBagConstraints.fill = java.awt.GridBagConstraints.HORIZONTAL;
        jPanel1.add(jButton2, gridBagConstraints);

        getContentPane().add(jPanel1);

        jPanel6.setLayout(new java.awt.GridBagLayout());

        jLabel4.setText("IP address of database ");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel6.add(jLabel4, gridBagConstraints);

        tfDBIPAddress.setColumns(10);
        jPanel6.add(tfDBIPAddress, new java.awt.GridBagConstraints());

        jLabel5.setText("Database port ");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 0;
        gridBagConstraints.gridy = 2;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel6.add(jLabel5, gridBagConstraints);

        tfDBPort.setColumns(10);
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 2;
        jPanel6.add(tfDBPort, gridBagConstraints);

        jLabel6.setText("Database username ");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 0;
        gridBagConstraints.gridy = 3;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel6.add(jLabel6, gridBagConstraints);

        tfDBUsername.setColumns(10);
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 3;
        jPanel6.add(tfDBUsername, gridBagConstraints);

        jLabel7.setText("Database password ");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 0;
        gridBagConstraints.gridy = 4;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel6.add(jLabel7, gridBagConstraints);

        tfDBPassword.setColumns(10);
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 4;
        jPanel6.add(tfDBPassword, gridBagConstraints);

        jLabel8.setText(" default (newgenlib)");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 2;
        gridBagConstraints.gridy = 4;
        jPanel6.add(jLabel8, gridBagConstraints);

        jLabel9.setText("Database name ");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 0;
        gridBagConstraints.gridy = 1;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel6.add(jLabel9, gridBagConstraints);

        tfDBName.setColumns(10);
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 1;
        jPanel6.add(tfDBName, gridBagConstraints);

        getContentPane().add(jPanel6);

        jLabel10.setText("Importing the xml file is completed, to insert accession numbers");
        jPanel8.add(jLabel10);

        bnInsertAccessionnos.setText("click here");
        bnInsertAccessionnos.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bnInsertAccessionnosActionPerformed(evt);
            }
        });
        jPanel8.add(bnInsertAccessionnos);

        getContentPane().add(jPanel8);

        jPanel7.setLayout(new java.awt.BorderLayout());
        jPanel7.add(pbImportFile, java.awt.BorderLayout.CENTER);

        getContentPane().add(jPanel7);

        jPanel2.setLayout(new java.awt.GridBagLayout());

        jButton3.setText("Import the above file");
        jButton3.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton3ActionPerformed(evt);
            }
        });
        jPanel2.add(jButton3, new java.awt.GridBagConstraints());

        getContentPane().add(jPanel2);

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        // TODO add your handling code here:
        JFileChooser jfc = new JFileChooser("/home/siddartha/Share");
        jfc.setFileFilter(new FileFilter() {
            
            @Override
            public boolean accept(File f) {
                if (f.getName().contains("xml") || f.isDirectory()) {
                    return true;
                } else {
                    return false;
                }
            }
            
            @Override
            public String getDescription() {
                return "(.xml) MARCXML";
            }
        });
        int option = jfc.showOpenDialog(this);
        if (option == JFileChooser.APPROVE_OPTION) {
            jTextField1.setText(jfc.getSelectedFile().getAbsolutePath());
        }
        
    }//GEN-LAST:event_jButton1ActionPerformed
    
    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
        // TODO add your handling code here:
        refresh();
        int total = this.getTotalRecords();
        JOptionPane.showMessageDialog(null, "Found " + total + " records", "Records count", JOptionPane.INFORMATION_MESSAGE);
    }//GEN-LAST:event_jButton2ActionPerformed
    
    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
        // TODO add your handling code here:
        newThread nt = new newThread(this);
        Thread th = new Thread(nt, "newThread");
        th.start();
    }//GEN-LAST:event_jButton3ActionPerformed
    
    private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton4ActionPerformed
        // TODO add your handling code here:
        JFileChooser jfc = new JFileChooser();
        jfc.addChoosableFileFilter(new FileFilter() {
            
            @Override
            public boolean accept(File f) {
                if (f.getName().toLowerCase().contains("xls") || f.isDirectory()) {
                    return true;
                } else {
                    return false;
                }
            }
            
            @Override
            public String getDescription() {
                return "xls";
            }
        });
        jfc.addChoosableFileFilter(new FileFilter() {
            
            @Override
            public boolean accept(File f) {
                if (f.getName().toLowerCase().contains("xlsx") || f.isDirectory()) {
                    return true;
                } else {
                    return false;
                }
            }
            
            @Override
            public String getDescription() {
                return "xlsx";
            }
        });
        int sel = jfc.showOpenDialog(this);
        if (sel == JFileChooser.APPROVE_OPTION) {
            tfExcelSheet.setText(jfc.getSelectedFile().getAbsolutePath());
        }
    }//GEN-LAST:event_jButton4ActionPerformed
    
    private void jButton5ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton5ActionPerformed
        // TODO add your handling code here:
        dialogSaveISBNs.setVisible(true);
    }//GEN-LAST:event_jButton5ActionPerformed
    
    private void bnISBNFileLocationActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bnISBNFileLocationActionPerformed
        // TODO add your handling code here:
        File createFile = null;
        JFileChooser chooser = new JFileChooser(new File(System.getProperty("user.home")));
        chooser.setFileFilter(new FileFilter() {

//            @Override
            public String getDescription() {
                return ".txt";
            }

//            @Override
            public boolean accept(File file) {
                //throw new UnsupportedOperationException("Not supported yet.");
                boolean status = false;
                try {
                    String fileName = file.getName().toLowerCase();
                    status = fileName.endsWith(".txt");
                } catch (Exception e) {
                    e.printStackTrace();
                }
                
                return status;
                
            }
        });
        int i = chooser.showSaveDialog(this);
        chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
        if (i == JFileChooser.APPROVE_OPTION) {
            
            String file = chooser.getSelectedFile().toString();
            StringTokenizer str = new StringTokenizer(file, ".");
            if (str.countTokens() <= 2) {
                
                if (str.countTokens() == 1) {
                    createFile = new File(chooser.getSelectedFile().toString() + ".txt");
                    if (createFile.exists()) {
                        int cnt = JOptionPane.showConfirmDialog(this, "This file already exists ! Are you sure \n you want to over write it.", "check", JOptionPane.OK_CANCEL_OPTION);
                        if (cnt == 0) {
                            tfTxtFilePath.setText(createFile.toString());
//                            System.out.println("override");
                        } else {
                            createFile = null;
                            tfTxtFilePath.setText("");
                        }
                    } else {
                        tfTxtFilePath.setText(createFile.toString());
                    }
                } else {
                    str.nextToken();
                    String s1 = str.nextToken(".");
                    if (s1.equalsIgnoreCase("txt")) {
                        createFile = new File(chooser.getSelectedFile().toString());
                        if (createFile.exists()) {
                            int cnt = JOptionPane.showConfirmDialog(this, "This file already exists ! Are you sure \n you want to over write it.", "check", JOptionPane.OK_CANCEL_OPTION);
                            if (cnt == 0) {
                                tfTxtFilePath.setText(createFile.toString());
//                                System.out.println("override");
                            } else {
                                createFile = null;
                                tfTxtFilePath.setText("");
                            }
                        } else {
                            tfTxtFilePath.setText(createFile.toString());
                        }
                    } else {
                        JOptionPane.showMessageDialog(this, "The given file is not in .txt format \n Please create .txt extension.", "check", JOptionPane.CANCEL_OPTION);
                    }
                }
                
            } else {
                JOptionPane.showMessageDialog(this, "The given file name is not correct \n Please create a new file.", "check", JOptionPane.YES_OPTION);
            }
        } else {
            tfTxtFilePath.setText("");
        }
        try {
            if (createFile != null) {
                OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream(tfTxtFilePath.getText()));
                osw.flush();
                osw.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }//GEN-LAST:event_bnISBNFileLocationActionPerformed
    
    private void bnISBNSaveOkActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bnISBNSaveOkActionPerformed
        // TODO add your handling code here:
        org.apache.poi.ss.usermodel.Workbook wb = null;
        if (tfExcelSheet.getText().toLowerCase().endsWith(".xls")) {
            try {
                wb = new HSSFWorkbook(new FileInputStream(tfExcelSheet.getText()));
            } catch (Exception ex) {
            }
        } else if (tfExcelSheet.getText().toLowerCase().endsWith("xlsx")) {
            try {
                wb = new XSSFWorkbook(new FileInputStream(new File(tfExcelSheet.getText())));
            } catch (Exception e) {
            }
        }
        if (wb != null) {
            PrintWriter pw = null;
            try {
                pw = new PrintWriter(new File(tfTxtFilePath.getText()));
                Sheet sheet = wb.getSheetAt(0);
                int isbnIndex = 0;
                for (Row row : sheet) {
                    String isbn = "";
                    for (Cell cell : row) {
                        cell.setCellType(1);
                        if (cell.getStringCellValue() != null && cell.getStringCellValue().equals("ISBN")) {
                            isbnIndex = cell.getColumnIndex();
                        }
                        if (cell.getColumnIndex() == isbnIndex) {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
                                isbn = String.valueOf((int) cell.getNumericCellValue());
                            } else {
                                isbn = cell.toString();
                            }
                        }
                    }
                    String filteredIsbn = getFilteredISBN(isbn);
                    pw.write(filteredIsbn);
                    pw.println();
                }
            } catch (FileNotFoundException ex) {
                Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                pw.close();
            }
        }
        this.dialogSaveISBNs.dispose();
    }//GEN-LAST:event_bnISBNSaveOkActionPerformed
    
    private void bnCancelActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bnCancelActionPerformed
        // TODO add your handling code here:
        this.dialogSaveISBNs.dispose();
    }//GEN-LAST:event_bnCancelActionPerformed
    
    private void bnInsertAccessionnosActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bnInsertAccessionnosActionPerformed
        // TODO add your handling code here:
        insertAccessionNumbers();
        indexDatabase();
    }//GEN-LAST:event_bnInsertAccessionnosActionPerformed
    public void indexDatabase() {
        try {
            NewBibliographicSolrIndexCreator.getInstance().deleteData();
            NewBibliographicSolrIndexCreator.getInstance().indexingData();
            CorporateNameSolrIndexCreator.getInstance().indexData();
            FormGenreSolrIndexCreator.getInstance().indexData();
            MeetingNameSolrIndexCreator.getInstance().indexData();
            PersonalNameSolrIndexCreator.getInstance().indexData();
            SubGeoNameSolrIndexCreator.getInstance().indexData();
            SubTopicSolrIndexCreator.getInstance().indexData();
            UniformTitleSolrIndexCreator.getInstance().indexData();
        } catch (Exception exp) {
            System.out.println("Error in indexing !!!!!!!!!!!");
        }
    }
    
    public void insertAccessionNumbers() {
        try {
            
                Class.forName("org.postgresql.Driver");
            this.con = DriverManager.getConnection("jdbc:postgresql://" + tfDBIPAddress.getText() + ":" + tfDBPort.getText() + "/" + tfDBName.getText(), tfDBUsername.getText(), tfDBPassword.getText());
            
            org.apache.poi.ss.usermodel.Workbook wb = null;
            
            if (tfExcelSheet.getText().toLowerCase().endsWith(".xls")) {
                try {
                    wb = new HSSFWorkbook(new FileInputStream(tfExcelSheet.getText()));
                } catch (Exception ex) {
                }
            } else if (tfExcelSheet.getText().toLowerCase().endsWith("xlsx")) {
                try {
                    wb = new XSSFWorkbook(new FileInputStream(new File(tfExcelSheet.getText())));
                } catch (Exception e) {
                }
            }
            if (wb != null) {
                Sheet sheet = wb.getSheetAt(0);
                int accessionNoIndex = 0;
                int bookNoIndex = 0;
                int classificationNoIndex = 0;
                int isbnIndex = 0;
                int shelvingLocationIndex = 0;
                for (Row row : sheet) {
                    for (Cell cell : row) {
                        if (cell.getStringCellValue() != null && cell.getStringCellValue().equals("AccessionNumber")) {
                            accessionNoIndex = cell.getColumnIndex();
                        } else if (cell.getStringCellValue() != null && cell.getStringCellValue().equals("BookNumber")) {
                            bookNoIndex = cell.getColumnIndex();
                        } else if (cell.getStringCellValue() != null && cell.getStringCellValue().equals("ClassificationNumber")) {
                            classificationNoIndex = cell.getColumnIndex();
                        } else if (cell.getStringCellValue() != null && cell.getStringCellValue().equals("ISBN")) {
                            isbnIndex = cell.getColumnIndex();
                        } else if (cell.getStringCellValue() != null && cell.getStringCellValue().equals("ShelvingLocation")) {
                            shelvingLocationIndex = cell.getColumnIndex();
                        }
                    }
                    break;
                }
                System.out.println(accessionNoIndex + "--" + bookNoIndex + "--" + classificationNoIndex + "--" + isbnIndex + "--" + shelvingLocationIndex);
                for (Row row : sheet) {
                    String accessionNo = "";
                    String bookNo = "";
                    String classificationNo = "";
                    String isbn = "";
                    String shelvingLocation = "";
                    for (Cell cell : row) {
                        // Do something here
                        if (cell.getColumnIndex() == accessionNoIndex) {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
                                
                                int value = (int) cell.getNumericCellValue();
                                accessionNo = String.valueOf(value);
                                System.out.println(".........................in if" + accessionNo);
                            } else {
                                
                                accessionNo = cell.toString();
                                System.out.println(" in else................." + accessionNo);
                            }
                        } else if (cell.getColumnIndex() == bookNoIndex) {
                            bookNo = cell.toString();
                            bookNo = bookNo.replaceAll("'", "''");
                        } else if (cell.getColumnIndex() == classificationNoIndex) {
                            classificationNo = cell.toString();
                            classificationNo = classificationNo.replaceAll("'", "''");
                        } else if (cell.getColumnIndex() == isbnIndex) {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
                                isbn = String.valueOf((int) cell.getNumericCellValue());
                            } else {
                                isbn = cell.toString();
                            }
                        } else if (cell.getColumnIndex() == shelvingLocationIndex) {
                            shelvingLocation = cell.toString();
                            shelvingLocation = shelvingLocation.replaceAll("'", "''");
                        }
                        switch (cell.getCellType()) {
                        }
                    }
                    try {
                        SolrServer server = new CommonsHttpSolrServer("http://localhost:8080/apache-solr-bib");
                        SolrQuery query = new SolrQuery();
                        System.out.println("ISBN # " + isbn);
                        String filteredISBN = getFilteredISBN(isbn);
                        System.out.println("Filtered ISBN # " + filteredISBN);
                        if (filteredISBN != null && !filteredISBN.equals("")) {
                            query.setQuery("020_Text:" + filteredISBN);
                        }
                        System.out.println("Query ### " + query.toString());
                        query.setStart(0);
                        QueryResponse qresp = server.query(query);
                        SolrDocumentList docs = qresp.getResults();
                        if (docs.size() > 0) {
                            String id = docs.get(0).getFieldValue("ID").toString();
                            StringTokenizer st = new StringTokenizer(id, "_");
                            Vector ids = new Vector();
                            while (st.hasMoreTokens()) {
                                ids.addElement(st.nextToken());
                            }
                            String catRecId = ids.get(0).toString();
                            String ownerLibId = ids.get(1).toString();
                            System.out.println("CatRecId # " + catRecId + " ********* OwnerLibId # " + ownerLibId);
                            String volumeId = "";
                            Statement stmt = this.con.createStatement();
                            ResultSet rs = stmt.executeQuery("select volume_id from cat_volume where cataloguerecordid = '" + catRecId + "' and owner_library_id = '" + ownerLibId + "'");
                            while (rs.next()) {
                                volumeId = rs.getString(1);
                                System.out.println("Volume id: "+volumeId);
                            }
                            rs.close();
                            stmt.close();
                            int locationId = 0;
                            stmt = this.con.createStatement();
                            rs = stmt.executeQuery("select location_id from location where location = '" + shelvingLocation + "'");
                            if (rs.next()) {
                                locationId = rs.getInt(1);
                            } else {
                                locationId = 1;
                            }
                            rs.close();
                            stmt.close();
                            String accNo = "";
                            String volId = "";
                            int locId = 0;
                            String bkNo = "";
                            String clssfctnNo = "";
                            StringTokenizer accTokens = new StringTokenizer(accessionNo, ",");
                            while (accTokens.hasMoreTokens()) {
                                String values = "";
                                if (accessionNo != null && !accessionNo.equals("")) {
                                    accNo = accTokens.nextToken().trim();
                                }
                                if (volumeId != null && !volumeId.equals("")) {
                                    volId = volumeId;
                                }
                                if (locationId != 0) {
                                    locId = locationId;
                                }
                                if (bookNo != null && !bookNo.equals("")) {
                                    bkNo = bookNo;
                                }
                                if (classificationNo != null && !classificationNo.equals("")) {
                                    clssfctnNo = classificationNo;
                                }
                                values = "'" + accNo + "', 1, '" + volId + "', '8', '" + locId + "', '" + accNo + "', '" + bkNo + "', '" + clssfctnNo + "', ";
                                if (!bkNo.equals("")) {
                                    values += "'" + clssfctnNo + " " + bookNo + "', 'B', '1', current_date, '', '1', '<Root/>'";
                                } else {
                                    values += "'" + clssfctnNo + "', 'B', '1', current_date, '', '1', '<Root/>'";
                                }
                                String columns = "accession_number, library_id, volume_id, material_type_id, location_id, barcode, book_number, classification_number, call_number, status, entry_id, entry_date, issue_details, entry_library_id, custom";
                                try {
                                    Statement insertStmt = this.con.createStatement();
                                    System.out.println("INSERT INTO document(" + columns + ") VALUES (" + values + ")");
                                    insertStmt.executeUpdate("INSERT INTO document(" + columns + ") VALUES (" + values + ")");
                                    insertStmt.close();
                                } catch (Exception ex) {
                                    System.out.println(accNo + "---" + ex.getMessage());
                                }
                            }
                        }
                        System.out.println("-----------------------------------------------New Row----------------------------------");
                    } catch (SolrServerException ex) {
                        ex.printStackTrace();
                    } catch (MalformedURLException ex) {
                        ex.printStackTrace();
                    }
                }
            }
            System.out.println("Successfully inserted");
            con.commit();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    public void saveRecord(CatalogMaterialDescription cmd, PersistMARCRecord psm) {
//        System.out.println(cmd);
        try {
            Field field = cmd.getField("020");
            String isbn = "";
            if (field != null) {
                SubField sf = field.getSubField('a');
                if (sf != null) {
                    isbn = sf.getData();
                    isbn = getFilteredISBN(isbn);
                }
            }
            SolrServer server = new CommonsHttpSolrServer("http://localhost:8080/apache-solr-bib");
            SolrQuery query = new SolrQuery();
            System.out.println("ISBN # " + isbn);
            String filteredISBN = getFilteredISBN(isbn);
            System.out.println("Filtered ISBN # " + filteredISBN);
            if (filteredISBN != null && !filteredISBN.equals("")) {
                query.setQuery("020_Text:" + filteredISBN);
            }
            System.out.println("Query ### " + query.toString());
            query.setStart(0);
            QueryResponse qresp = server.query(query);
            SolrDocumentList docs = qresp.getResults();
            if (docs.size() == 0) {
                Hashtable ht = psm.persist3_0(cmd, "1", "3", "");
                nbw.indexingOneRecordData(this.con,ht.get("Id").toString(), ht.get("LibId").toString());
            } else {
                System.out.println(" record already found");
            }
        } catch (Exception ex) {
        }
        
    }
    
    public int countOccurrences(String haystack, char needle) {
        int count = 0;
        for (int i = 0; i < haystack.length(); i++) {
            if (haystack.charAt(i) == needle) {
                count++;
            }
        }
        return count;
    }
    
    public String getFilteredISBN(String isbn) {
        String curedISBN = "";
        char[] chISBN = isbn.toCharArray();
        for (int i = 0; i < chISBN.length; i++) {
            if (Character.isDigit(chISBN[i])) {
                curedISBN += chISBN[i];
            }
        }
        return curedISBN;
    }
    
    public int getTotalRecords() {
        
        int total = 0;
        SwingWorker sw = new SwingWorker() {
            
            @Override
            protected Object doInBackground() throws Exception {
                try {
                    FileInputStream fis = new FileInputStream(jTextField1.getText());
                    XMLInputFactory factory = XMLInputFactory.newInstance();
                    XMLStreamReader parser = factory.createXMLStreamReader(fis);
                    int recordCountM = 0;
                    while (parser.hasNext()) {
                        String nameele = "";
                        try {
                            nameele = parser.getLocalName();
                        } catch (Exception ex) {
                        }
                        if (parser.getEventType() == XMLStreamConstants.START_ELEMENT && nameele != null && nameele.equals("record")) {
                            recordCountM++;
                        }
                        parser.next();
                    }
                    parser.close();
                    totalRecords = recordCountM;
                } catch (Exception e) {
                    e.printStackTrace();
                }
                return "";
            }
        };
        sw.run();
        if (sw.isDone()) {
            total = totalRecords;
//            System.out.println("Total records ### " + total);
        }
        return total;
    }
    
    public void refresh() {
        pbImportFile.setString("");
        pbImportFile.setValue(0);
        pbImportFile.setMaximum(0);
        pbImportFile.setStringPainted(true);
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            
            public void run() {
                new MainFrame().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton bnCancel;
    private javax.swing.JButton bnISBNFileLocation;
    private javax.swing.JButton bnISBNSaveOk;
    private javax.swing.JButton bnInsertAccessionnos;
    private javax.swing.JDialog dialogSaveISBNs;
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton2;
    public javax.swing.JButton jButton3;
    private javax.swing.JButton jButton4;
    private javax.swing.JButton jButton5;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel10;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JLabel jLabel8;
    private javax.swing.JLabel jLabel9;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel jPanel3;
    private javax.swing.JPanel jPanel4;
    private javax.swing.JPanel jPanel5;
    private javax.swing.JPanel jPanel6;
    private javax.swing.JPanel jPanel7;
    public javax.swing.JPanel jPanel8;
    public javax.swing.JTextField jTextField1;
    public javax.swing.JProgressBar pbImportFile;
    public javax.swing.JTextField tfDBIPAddress;
    public javax.swing.JTextField tfDBName;
    public javax.swing.JTextField tfDBPassword;
    public javax.swing.JTextField tfDBPort;
    public javax.swing.JTextField tfDBUsername;
    public javax.swing.JTextField tfExcelSheet;
    private javax.swing.JTextField tfTxtFilePath;
    // End of variables declaration//GEN-END:variables
    public int totalRecords;
}

class newThread implements Runnable {
    
    MainFrame mn;

    public newThread(MainFrame mn) {
        this.mn = mn;

    }
    
    @Override
    public void run() {
//        throw new UnsupportedOperationException("Not supported yet.");
        mn.refresh();
        mn.jButton3.setEnabled(false);
        try {
                        Class.forName("org.postgresql.Driver");
            Connection con = DriverManager.getConnection("jdbc:postgresql://" + mn.tfDBIPAddress.getText() + ":" + mn.tfDBPort.getText() + "/" + mn.tfDBName.getText(), mn.tfDBUsername.getText(), mn.tfDBPassword.getText());
            PersistMARCRecord psm = new PersistMARCRecord("1", "1", "1", "1", "1", con, true);
            FileInputStream fis = new FileInputStream(mn.jTextField1.getText());
            XMLInputFactory factory = XMLInputFactory.newInstance();
            XMLStreamReader parser = factory.createXMLStreamReader(fis);
            int recordCount = 0;
            int total = mn.getTotalRecords();
            CatalogMaterialDescription cmd = null;
            ArrayList alfields = null;
            ArrayList alControlFields = null;
            ArrayList alsubfields = null;
            ControlField currentcf = null;
            Field currentField = null;
            SubField currentSF = null;
            char currentSFIden = ' ';
            String currentSFData = "";
            FixedFieldProcessor ffp = null;
            String currentLeader = "";
            String currently = "";
            String currentTag = "";
            mn.pbImportFile.setMaximum(total - 1);
            while (parser.hasNext()) {
                int event = parser.getEventType();
//                System.out.println(parser.getEventType());
                String nameele = null;
                try {
                    nameele = parser.getLocalName();
                } catch (Exception ex) {
                }

                //System.out.println("nameele: " + nameele);
                switch (event) {
                    case XMLStreamConstants.START_ELEMENT:
                        if (nameele != null) {
                            if (nameele.equals("record")) {
                                ffp = new FixedFieldProcessor();
                                cmd = new CatalogMaterialDescription();
                                currentLeader = "";
                                alfields = new ArrayList();
                                alControlFields = new ArrayList();
                                recordCount++;
                            } else if (nameele.equals("controlfield")) {
                                currently = "controlfield";
//                                System.out.println("controlfield staretd");
                                int count = parser.getAttributeCount();
                                String tag = "";
                                for (int i = 0; i < count; i++) {
                                    tag = parser.getAttributeValue(i);
//                                    System.out.println(parser.getAttributeName(i) + "=" + parser.getAttributeValue(i));
                                }
                                currentcf = new ControlField();
                                currentcf.setTag(tag);
                            } else if (nameele.equals("datafield")) {
                                int count = parser.getAttributeCount();
                                String tag = "";
                                String i1 = "";
                                String i2 = "";
                                for (int i = 0; i < count; i++) {
                                    String attrname = parser.getAttributeName(i).getLocalPart();
                                    String aattrval = parser.getAttributeValue(i);
                                    if (attrname.equals("tag")) {
                                        tag = aattrval;
                                    }
                                    if (attrname.equals("ind1")) {
                                        i1 = aattrval;
                                    }
                                    if (attrname.equals("ind2")) {
                                        i2 = aattrval;
                                    }
                                    
                                }
                                
                                char i1c = ' ';
                                char i2c = ' ';
                                if (i1.length() > 0) {
                                    i1c = i1.charAt(0);
                                }
                                if (i2.length() > 0) {
                                    i2c = i2.charAt(0);
                                }
                                currentField = new Field(tag, i1c, i2c);
                                alsubfields = new ArrayList();
                                currentTag = tag;
                            } else if (nameele.equals("subfield")) {
                                currently = "subfield";
                                int count = parser.getAttributeCount();
                                String code = "";
                                for (int i = 0; i < count; i++) {
                                    code = parser.getAttributeValue(i);
                                }
                                currentSFIden = code.charAt(0);
                            } else if (nameele.equals("leader")) {
                                currently = "leader";
                                
                            }

//                        System.out.println("Starting: "+parser.getLocalName());
                        }
                        break;
                    case XMLStreamConstants.END_ELEMENT:
                        if (nameele != null) {
                            if (nameele.equals("controlfield")) {
                                alControlFields.add(currentcf);
                            } else if (nameele.equals("subfield")) {
                                currentSF = new SubField(currentSFIden, currentSFData);
                                alsubfields.add(currentSF);
                            } else if (nameele.equals("datafield")) {
                                currentField.addSubField(alsubfields);
                                if (!currentTag.startsWith("9")) {
                                    alfields.add(currentField);
                                }
                            } else if (nameele.equals("record")) {
                                cmd.setLeader(currentLeader);
                                cmd.addControlField(alControlFields);
                                cmd.addField(alfields);
                                cmd.setFixedField(ffp.fxld);
//                                System.out.println(cmd);
                                mn.saveRecord(cmd, psm);
                            } else if (nameele.equals("leader")) {
                                ffp.startLeader(new Leader(currentLeader));
                            }
                        }
                        break;
                    case XMLStreamConstants.CHARACTERS:
                        if (currently.equals("controlfield")) {
                            try {
                                currentcf.setData(parser.getText());
                                ffp.startControlField(currentcf.getTag(), currentcf.getData());
                            } catch (Exception ex) {
                                ex.printStackTrace();
                            }
                        }
                        if (currently.equals("subfield")) {
                            currentSFData = parser.getText();
                        }
                        if (currently.equals("leader")) {
                            currentLeader = parser.getText();
                        }
                        
                        break;
                    case XMLStreamConstants.CDATA:
                        break;
                } // end switch

                parser.next();
                mn.pbImportFile.setValue(recordCount);
                mn.pbImportFile.setString(recordCount + "/" + total);
            } // end while
            parser.close();
//            System.out.println("Total number of records: " + recordCount);
        } catch (Exception e) {
            e.printStackTrace();
        }
//        mn.indexDatabase();
        mn.jButton3.setEnabled(true);
        mn.refresh();
        //mn.jPanel8.setVisible(true);
    }
}
